package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageDialog;
import com.moleq.models.BottleSetting;
import com.moleq.models.Departmant;
import com.moleq.models.PosLineTares;
import com.moleq.models.PosTrns;
import com.moleq.models.TaxSetting;

public class PosTrnsService {
	private DatabaseHelper DBHelper;
	private MessageDialog mMessageDialog;
	Context context;
	
	public PosTrnsService() {

	}

	public PosTrnsService(Context context) {
		this.context = context;
		this.DBHelper = new DatabaseHelper(context);
	}
	
	public List<PosTrns> FindAll(){
		List<PosTrns> obj = new ArrayList<PosTrns>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from t_pos_trns",null);
		boolean lb_pt_appr = false;
		while(cursor.moveToNext()){
			int pt_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_NO));
			String pt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DATE));
			int pt_store_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STORE_NO));
			int pt_station  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATION));
			String pt_crt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_DATE));
			String pt_crt_time = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_TIME));
			String pt_done_date = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_DATE));
			String pt_done_time  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_TIME));
			String pt_status  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATUS));
			String pt_user_id  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_USER_ID));
			double pt_total  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TOTAL));
			double pt_tax  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX));
			double pt_tax_exem = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX_EXEM));
			String pt_cmr_birthday = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CMR_BIRTHDAY));
			String pt_type = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TYPE));
			String pt_manager_id = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_MANAGER_ID));
			String pt_appr = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_APPR));
			String pt_rounding_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING_FL));
			double pt_rounding = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING));
			
			if (pt_appr.equals("1"))  lb_pt_appr = true;
			
			obj.add(new PosTrns(pt_no,pt_date,pt_store_no,pt_station,pt_crt_date,pt_crt_time,pt_done_date,pt_done_time,pt_status,pt_user_id
					,pt_total,pt_tax,pt_tax_exem,pt_cmr_birthday,pt_type,pt_manager_id,lb_pt_appr,pt_rounding_fl,pt_rounding));
		}
		cursor.close();
		return obj;
	}
	
	public PosTrns Find(int no){
		
		PosTrns obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from t_pos_trns where pt_no = "+no,null);
		boolean lb_pt_appr = false;
		while(cursor.moveToNext()){
			int pt_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_NO));
			String pt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DATE));
			int pt_store_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STORE_NO));
			int pt_station  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATION));
			String pt_crt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_DATE));
			String pt_crt_time = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_TIME));
			String pt_done_date = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_DATE));
			String pt_done_time  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_TIME));
			String pt_status  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATUS));
			String pt_user_id  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_USER_ID));
			double pt_total  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TOTAL));
			double pt_tax  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX));
			double pt_tax_exem = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX_EXEM));
			String pt_cmr_birthday = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CMR_BIRTHDAY));
			String pt_type = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TYPE));
			String pt_manager_id = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_MANAGER_ID));
			String pt_appr = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_APPR));
			String pt_rounding_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING_FL));
			double pt_rounding = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING));
			
			if (pt_appr.equals("1"))  lb_pt_appr = true;
			
			obj = new PosTrns(pt_no,pt_date,pt_store_no,pt_station,pt_crt_date,pt_crt_time,pt_done_date,pt_done_time,pt_status,pt_user_id
					,pt_total,pt_tax,pt_tax_exem,pt_cmr_birthday,pt_type,pt_manager_id,lb_pt_appr,pt_rounding_fl,pt_rounding);
					
		}
		cursor.close();
		return obj;
	}
	
	public PosTrns FindLastTrn(){
		PosTrns obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from t_pos_trns where pt_status = 'P' order by pt_no desc limit 1",null);
		boolean lb_pt_appr = false;
		if(cursor.moveToNext()){
			int pt_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_NO));
			String pt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DATE));
			int pt_store_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STORE_NO));
			int pt_station  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATION));
			String pt_crt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_DATE));
			String pt_crt_time = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_TIME));
			String pt_done_date = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_DATE));
			String pt_done_time  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_TIME));
			String pt_status  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATUS));
			String pt_user_id  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_USER_ID));
			double pt_total  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TOTAL));
			double pt_tax  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX));
			double pt_tax_exem = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX_EXEM));
			String pt_cmr_birthday = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CMR_BIRTHDAY));
			String pt_type = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TYPE));
			String pt_manager_id = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_MANAGER_ID));
			String pt_appr = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_APPR));
			String pt_rounding_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING_FL));
			double pt_rounding = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING));
			
			if (pt_appr.equals("1"))  lb_pt_appr = true;
			
			obj = new PosTrns(pt_no,pt_date,pt_store_no,pt_station,pt_crt_date,pt_crt_time,pt_done_date
					,pt_done_time,pt_status,pt_user_id,pt_total,pt_tax,pt_tax_exem,pt_cmr_birthday,pt_type,pt_manager_id,lb_pt_appr,pt_rounding_fl,pt_rounding);
		}
		cursor.close();
		return obj;
	}
	
	public PosTrns GetTrn(int trn_no){
		PosTrns obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from t_pos_trns where pt_no = " + trn_no,null);
		boolean lb_pt_appr = false;
		if(cursor.moveToNext()){
			int pt_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_NO));
			String pt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DATE));
			int pt_store_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STORE_NO));
			int pt_station  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATION));
			String pt_crt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_DATE));
			String pt_crt_time = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_TIME));
			String pt_done_date = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_DATE));
			String pt_done_time  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_TIME));
			String pt_status  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATUS));
			String pt_user_id  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_USER_ID));
			double pt_total  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TOTAL));
			double pt_tax  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX));
			double pt_tax_exem = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX_EXEM));
			String pt_cmr_birthday = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CMR_BIRTHDAY));
			String pt_type = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TYPE));
			String pt_manager_id = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_MANAGER_ID));
			String pt_appr = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_APPR));
			String pt_rounding_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING_FL));
			double pt_rounding = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING));
			
			if (pt_appr.equals("1"))  lb_pt_appr = true;
			
			obj = new PosTrns(pt_no,pt_date,pt_store_no,pt_station,pt_crt_date,pt_crt_time,pt_done_date
					,pt_done_time,pt_status,pt_user_id,pt_total,pt_tax,pt_tax_exem,pt_cmr_birthday,pt_type,pt_manager_id,lb_pt_appr,pt_rounding_fl,pt_rounding);
		}
		cursor.close();
		return obj;
	}
	
	public int LastTrnNo(){
		int trnNo = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select pt_no from t_pos_trns where pt_status = 'P' order by pt_no desc limit 1",null);
		
		if(cursor.moveToNext()){
			trnNo = cursor.getInt(0);
		}
		cursor.close();
		return trnNo;
	}
	
	public int getMaxNo(){
		int no = 0;
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select max(pt_no) from " + DatabaseSchema.PosTrns.SCHEMA_NAME, null);
		if (cursor.moveToNext()) {
			no = cursor.getInt(0);
		}
		cursor.close();
		return no;
	}
	
	public void Insert(PosTrns obj){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"INSERT INTO " + DatabaseSchema.PosTrns.SCHEMA_NAME + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
				new Object[] { obj.getPt_no() ,obj.getPt_date(), obj.getPt_store_no(), obj.getPt_station(), obj.getPt_crt_date(), obj.getPt_crt_time(), 
						obj.getPt_done_date(), obj.getPt_done_time(), obj.getPt_status(), obj.getPt_user_id(), obj.getPt_total(), obj.getPt_tax(),
						obj.getPt_tax_exem(), obj.getPt_cmr_birthday(),obj.getPt_type(),obj.getPt_manager_id(),"0",
						obj.getPt_rounding_fl(),obj.getPt_rounding()});
	}
	
	public void Update(PosTrns obj){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"UPDATE " + DatabaseSchema.PosTrns.SCHEMA_NAME + " set pt_date = ?,pt_done_date = ?,pt_done_time = ?," +
						"pt_status = ?,pt_total = ?,pt_tax = ?,pt_tax_exem = ?,pt_user_id = ?,pt_type = ?,pt_manager_id = ? where pt_no = ?",
				new Object[] { obj.getPt_date(), obj.getPt_done_date(), obj.getPt_done_time(), obj.getPt_status(),obj.getPt_total(),
						obj.getPt_tax(), obj.getPt_tax_exem(), obj.getPt_user_id(), obj.getPt_type(), obj.getPt_manager_id(), obj.getPt_no() });
	}
	
	public void UpdateBirthday(String birthday,int trn_no){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"UPDATE " + DatabaseSchema.PosTrns.SCHEMA_NAME + " set pt_cmr_birthday = ? where pt_no = ?",
				new Object[] { birthday, trn_no });
	}
	
	public void UpdateTrnAppr(String appr,int trn_no){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"UPDATE " + DatabaseSchema.PosTrns.SCHEMA_NAME + " set pt_appr = ? where pt_no = ?",
				new Object[] { appr, trn_no });
	}
	
	public void UpdateRoundFlag(String round_fl,int trn_no){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"UPDATE " + DatabaseSchema.PosTrns.SCHEMA_NAME + " set pt_rounding_fl = ? where pt_no = ?",
				new Object[] { round_fl,trn_no });
	}
	
	public void UpdateRounding(double rounding,int trn_no){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"UPDATE " + DatabaseSchema.PosTrns.SCHEMA_NAME + " set pt_rounding = ? where pt_no = ?",
				new Object[] { rounding,trn_no });
	}
	
	public List<String[]> getSignatureTransByBeginDateToEndDate(String sStartDate, String sEndDate){
		List<String[]> obj = new  ArrayList<String[]>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select pt_no,pt_done_date,pt_done_time,ts_tender_id from t_pos_trns,t_tender_signature ";
		String sqlWhere = " where ts_trn_no = pt_no and pt_date >= " + sStartDate + " and pt_date <= " + sEndDate;
		String sqlGroup = " group by pt_no order by pt_no desc";
		sql = sql + sqlWhere+sqlGroup;
		
		Cursor cursor = db.rawQuery(sql,null);
		while(cursor.moveToNext()){
			int pt_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_NO));
//			String pt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DATE));
			String tendid = cursor.getString(cursor.getColumnIndex(DatabaseSchema.TenderSignature.TS_TENDER_ID));
//			int pt_store_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STORE_NO));
//			int pt_station  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATION));
//			String pt_crt_date  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_DATE));
//			String pt_crt_time = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CRT_TIME));
			String pt_done_date = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_DATE));
			String pt_done_time  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_DONE_TIME));
//			String pt_status  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_STATUS));
//			String pt_user_id  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_USER_ID));
//			double pt_total  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TOTAL));
//			double pt_tax  = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX));
//			double pt_tax_exem = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TAX_EXEM));
//			String pt_cmr_birthday = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_CMR_BIRTHDAY));
//			String pt_type = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_TYPE));
//			String pt_manager_id = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_MANAGER_ID));
//			String pt_appr = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_APPR));
//			String pt_rounding_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING_FL));
//			double pt_rounding = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.PosTrns.PT_ROUNDING));
			String[] str = {String.valueOf(pt_no),pt_done_date+pt_done_time,tendid};
			obj.add(str);
		}
		cursor.close();
		return obj;
	}
}
